package cn.edu.yzu.scholarship.model;

import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import cn.edu.yzu.scholarship.constant.C;
import cn.edu.yzu.scholarship.kit.Sak;
import cn.edu.yzu.scholarship.pojo.Pager;

import com.jfinal.ext.plugin.sqlinxml.SqlKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;


/**
 * 学生实体
 * @author francis
 */
public class Student extends Model<Student> {
	private static final long serialVersionUID = -4606134757095363955L;
	
	public static final Student dao = new Student();
	
	public static Record getRecord(String loginName, String loginPwd) {
		return Db.findFirst(SqlKit.sql("student.findByXhAndPwd"), loginName, loginPwd);
	}
		
	public static List<Record> getRecords() {
		return Db.find(SqlKit.sql("student.findAll"));
	}
	
	public static void update(Record record) {
		Db.update("student", record);
	}
	
	public static Pager<Record> paginate(int pageNumber,int pageSize) {
		return Pager.toPager(Db.paginate(pageNumber, pageSize, "select * ", "from student"));
	}
	
	public static Pager<Record> paginate(int pageNumber,int pageSize,Map<String,String> params) {
		String college = params.get("college");//学院
		String sql1 = C.PARAM_ALL.equals(college) ? "" : " and lqyxsmc in (select lqyxsmc from college where lqyxsm in (" + college + "))";
		
		String major = params.get("major");//专业
		String sql2 = C.PARAM_ALL.equals(major) ? "" : " and lqzymc in (select lqzymc from major where lqzydm in (" + major + "))";
		
		String sql3 = buildSQL(params.get("year"),params.get("classify"));//生成学号
		
		String groupId = params.get("groupId");
		String sql4 = C.PARAM_ALL.equals(groupId) ? "" : " and xh in (select xh from stugrouplist where groupId in (" + groupId + "))";
		
		return Pager.toPager(Db.paginate(pageNumber, pageSize, "select * ", "from student where 1 = 1 " + sql1 + sql2 + sql3 + sql4));
	}
	
	public static Pager<Record> paginateFree(int pageNumber,int pageSize,Map<String,String> params) {
		String college = params.get("college");//学院
		String sql1 = C.PARAM_ALL.equals(college) ? "" : " and lqyxsmc in (select lqyxsmc from college where lqyxsm in (" + college + "))";
		
		String major = params.get("major");//专业
		String sql2 = C.PARAM_ALL.equals(major) ? "" : " and lqzymc in (select lqzymc from major where lqzydm in (" + major + "))";
		
		String sql3 = buildSQL(params.get("year"),params.get("classify"));//生成学号
		
		String groupId = params.get("groupId");
		String sql4 = C.PARAM_ALL.equals(groupId) ? "" : " and xh in (select xh from stugrouplist where groupId in (" + groupId + "))";
		
		return Pager.toPager(Db.paginate(pageNumber, pageSize, "select student.*,freeze.reason ", "from student left join (select distinct xh,reason from freeze) freeze on student.xh = freeze.xh where 1 = 1 " + sql1 + sql2 + sql3 + sql4));
	}
	
	static String buildSQL(String year,String classify) {
		if(C.PARAM_ALL.equals(year) && C.PARAM_ALL.equals(classify)) {//不作限制
			return "";
		}
		
		if(C.PARAM_ALL.equals(year)) {//仅有classify
			return " and (xh like '" + StringUtils.join(classify.split(","),"%' or xh like '") + "%') ";
		}
		
		if(C.PARAM_ALL.equals(classify)) {//仅有year
			return " and (xh like 'M" + StringUtils.join(year.split(","),"%' or xh like 'M") + "%'" +
					" or xh like 'MZ" + StringUtils.join(year.split(","),"%' or xh like 'MZ") + "%'" +
					" or xh like 'N" + StringUtils.join(year.split(","),"%' or xh like 'N") + "%') ";
		}
		
		StringBuilder sql = new StringBuilder(" and (xh like '");
		
		String[] cs = classify.split(",");
		String[] ys = year.split(",");
		
		for(String c : cs) {
			for(String y : ys) {
				sql.append(c).append(y).append("%' or xh like '");
			}
		}
		
		sql.delete(sql.length() - "%' or xh like '".length(), sql.length());
		sql.append("%') ");
		
		return sql.toString();
	}
	
	private static final String[] columnKeys = new String[] {//其中姓名、学号、专业名称、学院名称、财务编码是必填项
		"xh", //学号*
		"xm", //姓名*
		"lqzymc", //录取专业名称*
		"lqzydm", //录取专业代码
		"csrq", //出生年月
		"xb", //性别
		"lxdh", //联系电话
		"yddh", //移动电话
		"dzxx", //电子邮箱
		"lqyxsmc", //学院名称*
		"lqyxsm", //学院代码
		"loginPwd", //登陆密码
		"cwbh", //财务编码*
		"lqlbm" //录取类别码
	};
	//(xh,xm,lqzymc,lqzydm,csrq,xb,lxdh,yddh,dzxx,lqyxsmc,lqyxsm,loginPwd,cwbh)
	
	private static final String[] leastKeys = new String[] {
		"xm", //姓名*
		"xh", //学号*
		"lqzymc", //录取专业名称*
		"lqyxsmc", //学院名称*
		"cwbh" //财务编码*
	};

	/**
	 * 检查导入的列（多还是少）
	 * 检查是否存在必填项为空
	        检查是否存在已录入信息（以学号判断，会覆盖原有记录）
	 * @param xls
	 * @return {data:{...},info:{}}
	 */
	public static Map<String,?> validate(List<String[]> xls) {
		if(xls == null || xls.size() < 1) {return Sak.newHashMap("info", Sak.newHashMap("error", true));}
		
		List<Map<String,Object>> data = buildData(xls);
		Map<String,Object> result = Sak.newHashMap("data", (Object)data);
		
		Map<String,Object> info = Sak.newHashMap();
		info.putAll(checkFirstRow(xls.get(0)));//检查列名
		info.putAll(checkDataIntegrity(data));//检查必填项的数值是否为空
		
		if(data.size() < 1) info.put("noRecord", true);//无记录
		else info.putAll(checkXhExists(data));//检查学号是否存在
		
		result.put("info", info);
		return result;
	}

	private static Map<String,Object> checkDataIntegrity(List<Map<String, Object>> data) {
		Map<String,Object> info = Sak.newHashMap();
		
		Set<String> emptyRowXh = Sak.newHashSet();
		for(Map<String,Object> row : data) {
			for(String key : leastKeys) {
				if(StringUtils.isBlank(String.valueOf(row.get(key)))) {
					info.put("containEmptyData", true);
					emptyRowXh.add(String.valueOf(row.get("xh")));
				}
			}
		}
		if(emptyRowXh.size() > 0) info.put("emptyRowXh", emptyRowXh);
		
		return info;
	}

	private static Map<String,Object> checkXhExists(List<Map<String, Object>> data) {
		Map<String,Object> info = Sak.newHashMap();
		if(!data.get(0).containsKey("xh")) return info;//不含学号列无需进行检查
		
		Set<String> existXh = Sak.newHashSet();
		for(Map<String,Object> map : data) {
			String xh = StringUtils.trim(String.valueOf(map.get("xh")));
			if(Db.findFirst("select * from student where xh = ?", xh) != null) {
				info.put("isXhExist", true);
				existXh.add(xh);
			}
		}
		
		if(existXh.size() > 0) info.put("existXh", existXh);
		
		return info;
	}

	private static List<Map<String, Object>> buildData(List<String[]> xls) {
		List<Map<String,Object>> data = Sak.newArrayList();
		
		try {
			for(int i = 1; i < xls.size(); i++) {
				Map<String,Object> row = Sak.newHashMap();
				for(int j = 0; j < xls.get(0).length; j++) {
					row.put(xls.get(0)[j], xls.get(i)[j]);
				}
				data.add(row);
			}
		} catch (Exception e) {// 出错，说明格式不对，将数据清空。
			data = Sak.newArrayList();
		}
		
		return data;
	}

	private static Map<String,?> checkFirstRow(String[] row) {
		Map<String,Object> info = Sak.newHashMap();
		Set<String> lostKeys = Sak.newHashSet();
		Set<String> lushKeys = Sak.newHashSet();
		
		for(int i = 0; i < leastKeys.length; i++) {
			if(ArrayUtils.indexOf(row, leastKeys[i]) < 0) {//检查导入的信息，是否包含最少的行数
				info.put("notContainLeastKeys", true);
				lostKeys.add(leastKeys[i]);
			}
		}
		
		for(int i = 0; i < row.length; i++) {
			if(ArrayUtils.indexOf(columnKeys, row[i]) < 0) {//检查导入的信息，是否包含多余的列，需提示客户，这些列无法被导入
				info.put("containLushKeys", true);
				lushKeys.add(row[i]);
			}
		}
		
		if(lostKeys.size() > 0) {info.put("lostKeys", StringUtils.join(lostKeys.toArray()));}
		if(lushKeys.size() > 0) {info.put("lushKeys", StringUtils.join(lushKeys.toArray()));}
		
		return info;
	}

	public static void importData(List<Map<String, Object>> data) {
		Object[][] params = new Object[data.size()][];
		
		for(int i = 0; i < data.size(); i++) {
			params[i] = new Object[columnKeys.length];
			for(int j = 0; j < columnKeys.length; j++) {
				params[i][j] = ObjectUtils.defaultIfNull(data.get(i).get(columnKeys[j]), "");
			}
		}
		
		Db.batch("insert into student(" + StringUtils.join(columnKeys,",") + ") values(?" + StringUtils.repeat(",?", columnKeys.length - 1) + ")", params, 30);
		// 更新学院代码 专业代码 登陆密码 录取类别名称
		Db.update("update student set lqyxsm = (select lqyxsm from college where lqyxsmc = student.lqyxsmc) where lqyxsm = ''");
		Db.update("update student set lqzydm = (select lqzydm from major where lqzymc = student.lqzymc) where lqzydm = ''");
		Db.update("update student set lqlbmc = '非定向' where lqlbmc is null and lqlbm = '11'");
		Db.update("update student set lqlbmc = '定向' where lqlbmc is null and lqlbm = '12'");
		Db.update("update student set lqlbmc = '委培' where lqlbmc is null and lqlbm = '23'");
		Db.update("update student set lqlbmc = '自筹' where lqlbmc is null and lqlbm = '24'");
		Db.update("update student set loginPwd = xh where loginPwd = ''");
	}
	
}
